﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Controls;
using System.Diagnostics;
using System.Xml;

namespace InscriberDataManager
{
    class Functionality
    {
        static private string dbServer = "localhost\\SQLExpress";
        static private string db = "DB";
        static private string db_user = "";
        static private string db_password = "";
        static private string db_config_tbl = "TB";
        static public string MaxRLLength = "50";
        static public int CurrentRLid = 0;
        static public int CurrentRLindex = 0;
        static public int CurrentLGid = 0;
        static public int CurrentLGindex = 0;
        static public int CurrentTRid = 0;
        static public int CurrentTRindex = 0;
        static public string CurrentRPstatus = "";
        static public int CurrentRPid = 0;
        static public int CurrentRPindex = 0;
        static public int CurrentLDid = 0;
        static public int CurrentLDindex = 0;
        static public int CurrentLDPartsid = 0;
        static public int CurrentLDPartsindex = 0;
        static public int CurrentANPartsid = 0;
        static public int CurrentANPartsindex = 0;

        public class Reports
        {
            public int id { get; set; }
            public string name { get; set; }
            public string status { get; set; }
        }

        public class Anonsai
        {
            public int id { get; set; }
            public string name { get; set; }
            public int report_id { get; set; }
        }

        public class AnonsaiParts
        {
            public int id { get; set; }
            public int anonso_id { get; set; }
            public string tekstas { get; set; }
            
        }

        public class Lydai
        {
            public int id { get; set; }
            public string title { get; set; }
            public string template { get; set; }
            public int report_id { get; set; }
        }

        public class LydaiParts
        {
            public int id { get; set; }
            public string tekstas1 { get; set; }
            public string tekstas2 { get; set; }
            public int lead_id { get; set; }

        }

        public class RuningLines
        {
            public int id { get; set; }
            public string Line { get; set; }
            public string End { get; set; }
            public string Type { get; set; }
        }

        public class Titras
        {
            public int id { get; set; }
            public string asmuo { get; set; }
            public string pareigos { get; set; }
            public string start_time { get; set; }
            public string end_time { get; set; }
            public int report_id { get; set; }
        }

        public class logo
        {
            public int id { get; set; }
            public string name { get; set; }
            public string tekstas { get; set; }
            public string template { get; set; }
            public string start_time { get; set; }
            public string end_time { get; set; }
            public int report_id { get; set; }
            public string reverse { get; set; }
        }

        public class logosTemplates
        {
            public int id { get; set; }
            public string TemplateName { get; set; }
            public string Template { get; set; }
        }


        public static string StartPath
        {
            get
            {
                return System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase).Substring(6);
            }
        }

        public static void readXMLParameters()
        {
            try
            {
                XmlTextReader textReader = new XmlTextReader(StartPath + "\\config.xml");

                while (textReader.Read())
                {
                    XmlNodeType nType = textReader.NodeType;
                    if (nType == XmlNodeType.Element)
                    {
                        if (textReader.Name.ToString().Equals("DataBaseServer"))
                        {
                            dbServer = textReader.ReadElementContentAsString();
                        }
                        if (textReader.Name.ToString().Equals("DataBaseName"))
                        {
                            db = textReader.ReadElementContentAsString();
                        }
                        if (textReader.Name.ToString().Equals("DataBaseUser"))
                        {
                            db_user = textReader.ReadElementContentAsString();
                        }
                        if (textReader.Name.ToString().Equals("DataBasePassword"))
                        {
                            db_password = textReader.ReadElementContentAsString();
                        }
                        if (textReader.Name.ToString().Equals("ConfigTable"))
                        {
                            db_config_tbl = textReader.ReadElementContentAsString();
                        }

                        if (textReader.Name.ToString().Equals("MaxRLLength"))
                        {
                            MaxRLLength = textReader.ReadElementContentAsString();
                        }

                    }
                }
            }
            catch (Exception ex) { AddToEventLog("InscriberDataManager", "XML skaitymo klaida: " + ex.Message, true); }
        }
        public static void AddToEventLog(string sSource, string sEvent, bool error)
        {
            string sLog = "Application";

            if (!EventLog.SourceExists(sSource))
                EventLog.CreateEventSource(sSource, sLog);
            if (error)
            {
                EventLog.WriteEntry(sSource, sEvent, EventLogEntryType.Error, 0);
            }
            else
            {
                EventLog.WriteEntry(sSource, sEvent, EventLogEntryType.Information, 123);
            }

        }


        public static void getreports(ListBox ls)
        {
            object ic = null;
           // int location = ls.SelectedIndex; 
            if (ls.SelectedIndex>=0)
              ic=ls.Items[ls.SelectedIndex];

            ls.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from reports order by name asc");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                     int id = int.Parse(tb.Rows[i]["id"].ToString());
                     string name = tb.Rows[i]["name"].ToString();
                     string status = tb.Rows[i]["status"].ToString();
                     ls.Items.Add(new Reports() { id = id, name = name, status = status });
                }
            }
            int location =0;
            List<Reports> newls = ls.Items.Cast<Reports>().ToList();
            if (ic != null)
                location = newls.FindIndex(rep => rep.name.Equals(((Reports)ic).name, StringComparison.Ordinal));
            ls.SelectedIndex=location;
        }

        public static void removeReportById(int id)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            dbc.sqlExecute("delete from reports where id=" + id.ToString());
            dbc.sqlExecute("delete from titrai where report_id=" + id.ToString());
            dbc.sqlExecute("delete from titrai where report_id=" + id.ToString());
            try
            {
                System.Data.DataTable tb = dbc.getTable("select * from leads where report_id=" + id.ToString());
                string lead_id = tb.Rows[0]["id"].ToString();
                
                dbc.sqlExecute("delete from leads_part where lead_id=" + lead_id);
            }
            catch (Exception ex) { }
            dbc.sqlExecute("delete from leads where report_id=" + id.ToString());
            dbc.sqlExecute("delete from logos where report_id=" + id.ToString());
            dbc = null;
        }

        public static void saveReports(ListBox ls)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            for (int i = 0; i < ls.Items.Count; i++)
            {
                Functionality.Reports rp = (Functionality.Reports)ls.Items[i];
                if (rp.id == 0)
                {
                    dbc.sqlExecute("insert into reports (name,status) values('" + rp.name + "','" + rp.status + "')");
                }
                else
                {
                    dbc.sqlExecute("update reports set name='"+rp.name+"' where id="+rp.id);
                }
            }
            dbc = null;
        }

        public static void getRuningLines(ListBox ls,string group)
        {
            ls.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "";
            if (group.Equals("VISOS"))
            {
                sql = "SELECT * FROM LIETUVA_RL UNION SELECT * FROM PASAULIS_RL UNION SELECT * FROM VERSLAS_RL UNION SELECT * FROM KRIMINALAI_RL UNION SELECT * FROM SPORTAS_RL ORDER BY sortorder,id asc";
            }
            else
            {
                sql = "select * from " + group + "_RL order by id asc";
            }
            System.Data.DataTable tb = dbc.getTable(sql);
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    string line = tb.Rows[i]["RL_line"].ToString();
                    string end = tb.Rows[i]["RL_end_symbol"].ToString();
                  /*  if (end.Equals("##0"))
                    {
                        end = "Nėra";
                    }*/
                    if (end.Equals("##1"))
                    {
                        end = "Toliau";
                    }
                    if (end.Equals("##2"))
                    {
                        end = "Pabaiga";
                    }
                    string type = tb.Rows[i]["RL_type"].ToString();
                    ls.Items.Add(new RuningLines() { id = id, Line = line, End=end, Type = type });
                }
            }
        }

        public static void getRuningLinesTypes(ComboBox cb)
        {
            cb.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from RunningLinesTypes");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    string name = tb.Rows[i]["TypeName"].ToString();
                    cb.Items.Add(name);
                }
                cb.Text = tb.Rows[0]["TypeName"].ToString();
            }
        }

        public static void removeRuningLineById(int id,string group)
        {
           // DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
           // dbc.sqlExecute("delete from "+group+" where id=" + id.ToString());
           // dbc = null;
        }

        public static void saveRuningLine(ListBox ls,TextBox tx, ComboBox cb,ComboBox cb1,string group)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
           /* string end = "##2";
            if (cb1.Text.Equals("Nėra"))
            {
                end = "##0";
            }
            if (cb1.Text.Equals("Pradžia"))
            {
                end = "##1";
            }
            if (cb1.Text.Equals("Pabaiga"))
            {
                end = "##2";
            }
                
            if (CurrentRLid == 0)
            {*/
            string sql = "";
            if (group.Equals("VISOS"))
            {
                sql = "SELECT * FROM LIETUVA_RL UNION SELECT * FROM PASAULIS_RL UNION SELECT * FROM VERSLAS_RL UNION SELECT * FROM KRIMINALAI_RL UNION SELECT * FROM SPORTAS_RL ORDER BY RL_type,id asc";
            }
            else
            {
                sql = "select * from " + group + "_RL";
            }
               dbc.sqlExecute("delete from " + group + "_RL where id>0");
                for (int i = 0; i < ls.Items.Count; i++)
                {
                    RuningLines rl = (RuningLines)ls.Items[i];
                    string end = "##1";
                   /* if (rl.End.Equals("Nėra"))
                    {
                        end = "##0";
                    }*/
                    if (rl.End.Equals("Toliau"))
                    {
                        end = "##1";
                    }
                    if (rl.End.Equals("Pabaiga"))
                    {
                        end = "##2";
                    }
                    dbc.sqlExecute("insert into " + group + "_RL (RL_line,RL_type,RL_end_symbol) values('" + rpl(rl.Line) + "','" + rl.Type + "','" + end + "')");
                    // dbc.sqlExecute("insert into RunningLines (RL_line,RL_type,RL_end_symbol) values('" + tx.Text + "','" + cb.Text + "','" + end + "')");
                }
        /*    }
            else
            {
                if (CurrentRLindex!=-1){
                Functionality.RuningLines rl = (Functionality.RuningLines)ls.Items[CurrentRLindex];
                string sql = "update RunningLines set RL_line='" + tx.Text + "', RL_type='" + cb.Text + "', RL_end_symbol='"+end+"' where id=" + rl.id.ToString();
                dbc.sqlExecute(sql);
                }
            }*/
            CurrentRLid = 0;
            CurrentRLindex = -1;
     
            dbc = null;
        }

        public static string[] splitToLine(string text)
        {
            int ln = text.Length;
            if ((ln % 56 == 0) || ((ln % 56 >= 50) && ln>56))
            {
                ln = ln + 50;
            }
            int modln = ln % 56;
            if (modln > 0)
            {
                ln = ln / 56 + 1;
            }
            else
            {
                ln = ln / 56;
            }
            string[] res = new string[ln];
            string[] words = text.Split(' ');
            int ix = 0;
            for (int y = 0; y < words.Length; y++)
            {
                res[ix] = res[ix] + words[y] + " ";
                string tmpline=res[ix];
                if (words.Length-1!=y){
                    tmpline=tmpline+words[y+1] + " ";
                    if (tmpline.Length > 56)
                    {
                        ix = ix + 1;
                    }
                }

            }
            return res;
        }

        public static void insertRuningLine(ListBox ls, TextBox tx, ComboBox cb, ComboBox cb1)
        {
            if (CurrentRLindex== 0)
            {
                if (tx.Text.Length <= 56)
                {
                    ls.Items.Add(new RuningLines() { Line = tx.Text.ToUpper(), Type = cb.Text, End = "Pabaiga" });
                }
                else
                {
                    string[] lines = splitToLine(tx.Text);
                    for (int i = 0; i < lines.Length; i++)
                    {
                        string end = cb1.Text;
                        if (i == lines.Length - 1)
                        {
                            end = "Pabaiga";
                        }
                        else
                        {
                            end = "Toliau";
                        }
                        if (lines[i] == null)
                        {
                            lines[i] = "";
                        }
                        ls.Items.Add(new RuningLines() { Line = lines[i].ToUpper(), Type = cb.Text, End = end });
                    }
                }
            }
            else
            {
                if (CurrentRLindex != -1)
                {
                    RuningLines rl = new RuningLines() { Line = tx.Text.ToUpper(), Type = cb.Text, End = cb1.Text };
                    ls.Items[CurrentRLindex] = rl;
                }
            }
        }

        public static void getAnonsaiByRepId(string rep_id,ListBox ls)
        {
            ls.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from anonsai where report_id=" + rep_id + " order by id asc");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    string name = tb.Rows[i]["name"].ToString();
                    int reps_id = int.Parse(tb.Rows[i]["report_id"].ToString());
                    ls.Items.Add(new Anonsai() { id = id, name = name, report_id = reps_id });
                }
            }
        }

        public static void getAnonsaiPartsByANId(string an_id, ListBox ls)
        {
            ls.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from anonsai_tekstai where anonso_id=" + an_id + " order by id asc");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    int ans_id = int.Parse(tb.Rows[i]["anonso_id"].ToString());
                    string tekstas = tb.Rows[i]["tekstas"].ToString();
                    ls.Items.Add(new AnonsaiParts() { id = id, anonso_id = ans_id, tekstas = tekstas });
                }
            }
        }

        public static void AddAnonsas(string report_id,string text)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            dbc.sqlExecute("insert into anonsai (name,txt_template,template,status,report_id) values ('" + text + "','anonsu_tekstas','anonsu_titras',0," + report_id + ")");
        }

        public static void AddAnonsasPart(string anonso_id, string text)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "insert into anonsai_tekstai (anonso_id,tekstas) values (" + anonso_id + ",'" + text + "')";
            dbc.sqlExecute(sql);
        }

        public static void removeAnonsaiById(int id)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "delete from anonsai where id="+id.ToString();
            dbc.sqlExecute(sql);
            sql = "delete from anonsai_tekstai where anonso_id=" + id.ToString();
            dbc.sqlExecute(sql);
        }

        public static void AnonsaiPartsById(int id)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "delete from anonsai_tekstai where id=" + id.ToString();
            dbc.sqlExecute(sql);
        }

        public static void getLydaiPartsByANId(string ld_id, ListBox ls)
        {
            ls.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from leads_part where lead_id=" + ld_id + " order by id asc");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    int ldd_id = int.Parse(tb.Rows[i]["lead_id"].ToString());
                    string tekstas1 = tb.Rows[i]["tekstas1"].ToString();
                    string tekstas2 = tb.Rows[i]["tekstas2"].ToString();
                    ls.Items.Add(new LydaiParts() { id = id, lead_id = ldd_id, tekstas1 = tekstas1, tekstas2=tekstas2 });
                }
            }
        }

        public static void getLydaiByRepId(string rep_id, ListBox ls)
        {
            ls.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from leads where report_id=" + rep_id+" order by id asc");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    string title = tb.Rows[i]["title"].ToString();
                    string template = tb.Rows[i]["template"].ToString();
                    int reps_id = int.Parse(tb.Rows[i]["report_id"].ToString());
                    ls.Items.Add(new Lydai() { id = id, title = title,template=template, report_id = reps_id});
                }
            }
        }

        public static void AddLydai(string rep_id, TextBox tx,CheckBox ch)
        {
            string tmp = "N";
            if (ch.IsChecked == true)
            {
                tmp = "K";
            }
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "insert into leads (title,template,part_duration,curent_part_id,status,report_id,parts_cnt) values ('" + rpl(tx.Text.ToUpper().Replace("'", "''")) + "','" + tmp + "','3000',0,0,'" + rep_id + "',0)";
            dbc.sqlExecute(sql);
        
        }

        public static void removeLydaiById(int id)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "delete from leads where id=" + id.ToString();
            dbc.sqlExecute(sql);
            sql = "delete from leads_part where lead_id=" + id.ToString();
            dbc.sqlExecute(sql);
        }

        public static void AddLydaiPart(string lead_id, string tekstas1, string tekstas2)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "insert into leads_part (tekstas1,tekstas2,template,lead_id) values ('" + tekstas1 + "','" + tekstas2 + "','lydas_tekst'," + lead_id + ")";
            dbc.sqlExecute(sql);
        }

        public static void removeLydaiPartsById(int id)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "delete from leads_part where id=" + id.ToString();
            dbc.sqlExecute(sql);
        }

        public static void updateLydaiCount(string id, ListBox ls)
        {
            int cnt = ls.Items.Count;
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "update leads set parts_cnt="+cnt+" where id=" + id.ToString();
            dbc.sqlExecute(sql);
        }

        public static void getLogoByRepId(string rep_id, ListBox ls)
        {
            ls.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from logos where report_id=" + rep_id + " order by id asc");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    string name = tb.Rows[i]["name"].ToString();
                    string tekstas = tb.Rows[i]["tekstas"].ToString();
                    string template = tb.Rows[i]["template"].ToString();
                    string start_time = tb.Rows[i]["start_time"].ToString().Substring(3, 5);
                    string end_time = tb.Rows[i]["end_time"].ToString().Substring(3, 5);
                    int reps_id = int.Parse(tb.Rows[i]["report_id"].ToString());
                    string reverse = tb.Rows[i]["reverse"].ToString();
                    ls.Items.Add(new logo() { id = id, name=name, tekstas=tekstas, template=template, start_time=start_time, end_time=end_time, report_id = reps_id, reverse=reverse });
                }
            }
        }

        public static void getTitraiByRepId(string rep_id, ListBox ls)
        {
            ls.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from titrai where report_id=" + rep_id + " order by id asc");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    string asmuo = tb.Rows[i]["asmuo"].ToString();
                    string pareigos = tb.Rows[i]["pareigos"].ToString();
                    string start_time = tb.Rows[i]["start_time"].ToString().Substring(3,5);
                    string end_time = tb.Rows[i]["end_time"].ToString().Substring(3, 5);
                    int reps_id = int.Parse(tb.Rows[i]["report_id"].ToString());
                    ls.Items.Add(new Titras() { id = id, asmuo=asmuo, pareigos=pareigos, start_time = start_time, end_time = end_time, report_id = reps_id });
                }
            }
        }

        public static void removeLogoById(int id)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            dbc.sqlExecute("delete from logos where id=" + id.ToString());
            dbc = null;
        }

        public static void removeTitrasById(int id)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            dbc.sqlExecute("delete from titrai where id=" + id.ToString());
            dbc = null;
        }

        public static string rpl(string tx)
        {
            string rez = "";
            rez = tx.Replace("'", "''");
            return rez;
        }

        public static void AddLogo(string rep_id, string tx1, TextBox tx2,string template,TextBox tx3,TextBox tx4,CheckBox ch)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "insert into logos (name,tekstas,template,start_time,end_time,status,report_id,reverse) values ('" + tx1 + "','" + rpl(tx2.Text) + "','" + template + "','00:" + tx3.Text + "','00:" + tx4.Text + "',0," + rep_id + ",'"+ch.IsChecked.ToString()+"')";
            dbc.sqlExecute(sql);
        }

        public static void AddTitras(string rep_id, TextBox tx1, TextBox tx2, TextBox tx3, TextBox tx4)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "insert into titrai (asmuo,pareigos,template,start_time,end_time,status,report_id) values ('" + Functionality.rpl(tx1.Text.ToUpper()) + "','" + Functionality.rpl(tx2.Text.ToUpper()) + "','titras','00:" + tx3.Text + "','00:" + tx4.Text + "',0," + rep_id + ")";
            dbc.sqlExecute(sql);
        }


        public static void getLogoTemplates(ComboBox cb)
        {
            cb.Items.Clear();
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            System.Data.DataTable tb = dbc.getTable("select * from LogosTemplates");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    string template = tb.Rows[i]["Template"].ToString();
                    string templatename = tb.Rows[i]["TemplateName"].ToString();
                    cb.Items.Add(new logosTemplates() {id=id, Template=template, TemplateName=templatename });
                }
                //cb.Text = tb.Rows[0]["TypeName"].ToString();
            }
        }

        public static void EditTitras(TextBox tx1, TextBox tx2, TextBox tx3, TextBox tx4)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "update titrai set asmuo='" + Functionality.rpl(tx1.Text.ToUpper()) + "', pareigos='" + Functionality.rpl(tx2.Text.ToUpper()) + "',start_time='00:" + tx3.Text + "', end_time='00:" + tx4.Text + "' where id=" + CurrentTRid;
            dbc.sqlExecute(sql);
        }

        public static void EditLogo(string tx1, TextBox tx2, string template, TextBox tx3, TextBox tx4,CheckBox ch)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "update logos set name='"+rpl(tx1)+"', tekstas='"+rpl(tx2.Text)+"', template='"+template+"', start_time='00:"+tx3.Text+"', end_time='00:"+tx4.Text+"', reverse='"+ch.IsChecked.ToString()+"' where id="+CurrentLGid;
            dbc.sqlExecute(sql);
        }

        public static void removeAllRL()
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "delete from LIETUVA_RL where id>0";
            dbc.sqlExecute(sql);
            sql = "delete from PASAULIS_RL where id>0";
            dbc.sqlExecute(sql);
            sql = "delete from VERSLAS_RL where id>0";
            dbc.sqlExecute(sql);
            sql = "delete from KRIMINALAI_RL where id>0";
            dbc.sqlExecute(sql);
            sql = "delete from SPORTAS_RL where id>0";
            dbc.sqlExecute(sql);
        }

        public static void EditLydai(TextBox textBox5, CheckBox checkBox1)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string template = "N";
            if (checkBox1.IsChecked==true)
            {
                template = "K";
            }
            else
            {
                template = "N";
            }
            string sql = "update leads set title='" + Functionality.rpl(textBox5.Text.ToUpper()) + "', template='" + template + "' where id=" + CurrentLDid;
            dbc.sqlExecute(sql);
        }

        public static void EditLydaiParts(TextBox textBox7, TextBox textBox6)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "update leads_part set tekstas1='" + Functionality.rpl(textBox7.Text.ToUpper()) + "', tekstas2='" + Functionality.rpl(textBox6.Text) + "' where id=" + CurrentLDPartsid;
            dbc.sqlExecute(sql);
        }

        internal static void EditAnonsaiPartsByANid(string id, string tekstas)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "update anonsai_tekstai set tekstas='" + Functionality.rpl(tekstas) + "' where id=" + id;
            dbc.sqlExecute(sql);
        }

        public static bool checkTime(string time1, string time2)
        {
            bool rez = true;
            string[] startTime = time1.Split(':');
            string[] stopTime = time2.Split(':');
            int startMin = int.Parse(startTime[0]);
            int startSek = int.Parse(startTime[1]);
            int stopMin = int.Parse(stopTime[0]);
            int stopSek = int.Parse(stopTime[1]);
            if ((startMin > stopMin) && ((startMin <= stopMin) || (startSek > stopSek)))
            {
                rez = false;
            }
            return rez;
        }

        public static void insertLydas(Lydai ld, Reports rp)
        {
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            string sql = "insert into leads (title,template,part_duration,curent_part_id,status,report_id,parts_cnt) values ('" + rpl(ld.title.ToUpper().Replace("'", "''")) + "','" + ld.template+ "','3000',0,0,'" + rp.id + "',0)";
            dbc.sqlExecute(sql);
            System.Data.DataTable tb1 = dbc.getTable("select * from leads where title='" + rpl(ld.title.ToUpper().Replace("'", "''")) + "'");
            string lead_id = tb1.Rows[0]["id"].ToString();
            System.Data.DataTable tb = dbc.getTable("select * from leads_part where lead_id=" + ld.id + " order by id asc");
            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int id = int.Parse(tb.Rows[i]["id"].ToString());
                    int ldd_id = int.Parse(tb.Rows[i]["lead_id"].ToString());
                    string tekstas1 = tb.Rows[i]["tekstas1"].ToString();
                    string tekstas2 = tb.Rows[i]["tekstas2"].ToString();
                    sql = "insert into leads_part (tekstas1,tekstas2,template,lead_id) values ('" + tekstas1 + "','" + tekstas2 + "','lydas_tekst'," + lead_id + ")";
                    dbc.sqlExecute(sql);
                }
            }
        }

        public static void insertTitras(Titras tr, Reports rp)
        {
            string sql = "insert into titrai (asmuo,pareigos,template,start_time,end_time,status,report_id) values ('" + Functionality.rpl(tr.asmuo.ToUpper()) + "','" + Functionality.rpl(tr.pareigos.ToUpper()) + "','titras','00:" + tr.start_time+ "','00:" + tr.end_time + "',0," + rp.id + ")";
            DataBase dbc = new DataBase(dbServer, db_user, db_password, db);
            dbc.sqlExecute(sql);
        }
    }
}
